Always on可以建立可用性群組的HA或讀取級別，HA可用性群組可以對資料庫進行容錯轉移，讀取級別可用性群組是針對唯讀工作複製其他SQLServer Instance的資料庫。

每一組可用性資料庫都由可用性複本控制，分為「主要複本」與「次要複本」，主要複本會將主要資料庫交易紀錄檔傳送到每個次要資料庫，做資料庫的同步。

接下來我們開始建置Always on

測試環境

各節點需要安裝SQLServer完成

關閉防火牆

兩台server都必須安裝容錯叢集

兩台必須建立相同DNS尾碼

在兩節點添加FQDN，否則無法建立Windows叢集
C:\Windows\System32\drivers\etc\hosts

172.31.31.144 AG-primary.sqlha.com	
172.31.31.168 AG-second.sqlha.com

建立Windows叢集

開啟powershell

new-cluster -name sqlag –Node AG-primary,AG-second -StaticAddress 172.31.31.145 -NoStorage –AdministrativeAccessPoint DNS

各節點啟用高可用性

AG-Primary

--建立Master key
use master
go

create master key encryption by password='1qaz@WSX'
go

--建立憑證
create certificate sql_primary with subject='sql_primary'
go

--備份憑證
backup certificate sql_primary 
to file='H:\BK\sql_primary.cert'
go

--建立端點
create endpoint sql_endpoint
state=started
as tcp
(listener_port=5022)
for database_mirroring
( authentication=certificate sql_primary,
role=all,
encryption=required algorithm aes)
go

AG_second

--建立Master key
create master key encryption by password='1qaz@WSX';

--建立憑證
create certificate sql_second
with subject='sql_second'
go

--備份憑證
backup certificate sql_second
to file='G:\bk\sql_second.cert'
go

--建立端點
create endpoint sql_endpoint
state=started
as tcp
( listener_port=5022)
for database_mirroring
( authentication=certificate sql_second,
role=all,
encryption=required algorithm aes)
go

建立節點之間的信任關係

在AG-primary建立共用資料夾，以便傳輸憑證

將AG-primary憑證傳送到AG-seocond，AG-second憑證傳送到AG-primary

AG-primary

---建立login
create login secondLogin with password='P@ssw0rd'
go

create user secondUser  for login secondLogin
go

grant connect on endpoint::sql_endpoint to secondLogin
go

AG-second

create login primaryLogin with password='P@ssw0rd'
go

create user primaryUser for login primaryLogin
go


create certificate sql_primary
authorization primaryUser
from file='G:\bk\sql_primary.cert'
go

grant connect on endpoint::sql_endpoint to primaryLogin
go

部屬Availability Group

AG-primary

--建立資料庫
use master
go

create database test01
go
use test01
create table t1 (id int,name varchar(20));

--完整備份資料庫test01
backup database test01 to disk='G:\bk\test01.bak'

--建立可用性群組
use master
go

create availability group ag
with
( automated_backup_preference=primary,
  db_failover=off,
  dtc_support=none
  )
  for database test01
  replica on 
  'AG-primary' with
  ( endpoint_url='tcp://AG-primary.sqlha.com:5022',
	failover_mode=automatic,
	availability_mode=synchronous_commit,
	secondary_role
	(
	 allow_connections=no
	 )
),
'AG-second' with
	( endpoint_url='tcp://AG-second.sqlha.com:5022',
	  failover_mode=automatic,
	  availability_mode=synchronous_commit,
	  secondary_role
	  ( allow_connections=no
	  )
	)
	Go

AG-second必須加入Availability group

use master
go

ALTER AVAILABILITY GROUP ag JOIN
GO

在還原資料庫到AG-second前，必須在AG-primary執行log backup

BACKUP LOG test01 TO DISK = 'H:\bk\test01.trn'
GO

還原資料庫(norecovery模式)

use master
go
RESTORE DATABASE test01 FROM DISK = 'G:\bk\test01.bak' 
WITH NORECOVERY,
move 'test01' to 'E:\Data\test01.mdf',
move 'test01_log' to 'F:\log\test01.ldf'
go


restore log test01 from disk='G:\bk\test01.trn' with norecovery
go

--將資料庫加入ag
ALTER DATABASE test01 SET HADR AVAILABILITY GROUP = ag
GO

Always on 設定完成